-- =============================================
-- This stored procedure inserts a record to Publish_Request table to request content
-- to be published by publishing server.
--
-- Modificaitons
-- 06/12/2003    E.Tatsui      Created
-- =============================================

CREATE                 PROCEDURE [dbo].[amsp_CMRequestPublish]
    @InNavMenuID numeric = NULL,
  @InWebsiteKey uniqueidentifier = NULL,
  @InContactID numeric,
  @InPublishDescendants char(1) = 'N',
  @InForcePublishFlag char(1) = 'N',
  @InPublishRegenerateInd char(1) = 'P',
  @InMicrositeID numeric = 0,
  @InPublishRequestID numeric = NULL,
  @OutPublishRequestID numeric OUTPUT
AS
BEGIN

  IF @InNavMenuID IS NULL AND @InWebsiteKey IS NULL
    RAISERROR('Either NavMenuID or WebsiteKey is required', 16,1)  

  DECLARE
    @PublishRequestID numeric,
    @NavMenuID numeric,
    @WebsiteKey uniqueidentifier,
    @ContentID numeric,
    @PublishFrequency integer,
    @MinSort numeric(28,18),
    @MaxSort numeric(28,18),
    @PreFuseURL varchar(255),
    @PostFuseURL varchar(255),
    @NewPreFuseURL varchar(255),
    @NewPostFuseURL varchar(255),
    @NavContentGroupInd char(1),
    @ContentCount integer,
    @PreviousWebsiteKey uniqueidentifier,
    @SuperUserFlag bit

  -- First, find out if this user is a member of a super group as a nav creator or editor.
  SET @SuperUserFlag = 0
  SELECT @SuperUserFlag = 1
    FROM Content_Authority_Group a WITH (NOLOCK), Content_Authority_Producer b WITH (NOLOCK)
   WHERE a.ContentAuthorityGroupID = b.ContentAuthorityGroupID
     AND b.ContactID = @InContactID
     AND (b.NavCreatorFlag = 'Y' OR b.NavEditorFlag = 'Y')
     AND a.SuperGroupFlag = 'Y'

  SELECT @NavContentGroupInd = NavContentGroupInd,
         @WebsiteKey = WebsiteKey
    FROM Nav_Menu WITH (NOLOCK)
   WHERE NavMenuID = @InNavMenuID

  -- If PublishRequestID is not passed in, create a new request.
  IF @InPublishRequestID IS NULL BEGIN
    INSERT INTO Publish_Request
                (ContactID,
                 RequestDateTime,
                 NavMenuID)
    VALUES (@InContactID,
            CURRENT_TIMESTAMP,
            @InNavMenuID)
  
    SET @PublishRequestID = @@Identity
  END
  ELSE
    SET @PublishRequestID = @InPublishRequestID

  -- When this is publishing process.
  IF @InPublishRegenerateInd = 'P' BEGIN
    -- Build a temp table which holds all the nav items to publish.
    CREATE TABLE #NavMenu
                 (NavMenuID numeric,
                  SortOrder numeric(28,18),
                  ContentID numeric,
                  FilePath varchar(255),
                  WebsiteKey uniqueidentifier,
                  PreviousWebsiteKey uniqueidentifier,
                  MicrositeFlag char(1))

    -- When NavMenuID is specified.
    IF @InNavMenuID IS NOT NULL BEGIN
      -- Add the actual item that was specified.
      INSERT INTO #NavMenu
      SELECT NavMenuID,
             SortOrder,
             ContentID,
             FilePath,
             WebsiteKey,
             PreviousWebsiteKey,
             MicrositeFlag
        FROM Nav_Menu WITH (NOLOCK)
       WHERE NavMenuID = @InNavMenuID
    
      -- If decendants need to be published also, add them to the temp table.
      IF @InPublishDescendants = 'Y' BEGIN
        SELECT @MinSort = a.SortOrder,
               @MaxSort = (SELECT IsNull(Min(x.SortOrder),0)
                             FROM Nav_Menu x WITH (NOLOCK)
                             WHERE x.SortOrder > a.SortOrder
                               AND x.CategoryDepth <= a.CategoryDepth)
          FROM Nav_Menu a  WITH (NOLOCK)
         WHERE a.NavMenuID = @InNavMenuID
        -- Super user can publish anything.
        IF @SuperUserFlag = 1
          INSERT INTO #NavMenu
          SELECT a.NavMenuID,
                 a.SortOrder,
                 a.ContentID,
                 a.FilePath,
                 a.WebsiteKey,
                 a.PreviousWebsiteKey,
                 a.MicrositeFlag
            FROM Nav_Menu a WITH (NOLOCK)
           WHERE a.SortOrder > @MinSort
             AND a.SortOrder < @MaxSort
           ORDER BY a.SortOrder
        ELSE
          -- We only care about the ones this user has permission to publish.
          INSERT INTO #NavMenu
          SELECT a.NavMenuID,
                 a.SortOrder,
                 a.ContentID,
                 a.FilePath,
                 a.WebsiteKey,
                 a.PreviousWebsiteKey,
                 a.MicrositeFlag
            FROM Nav_Menu a WITH (NOLOCK), Content_Authority_Producer b WITH (NOLOCK)
           WHERE a.ContentAuthorityGroupID = b.ContentAuthorityGroupID
             AND b.ContactID = @InContactID
             AND (b.NavCreatorFlag = 'Y' OR b.NavEditorFlag = 'Y')
             AND a.SortOrder > @MinSort
             AND a.SortOrder < @MaxSort
           ORDER BY a.SortOrder
        END   
    END -- Ends when NavMenuID is specified

    ELSE BEGIN-- When WebsiteKey is specified
      INSERT INTO #NavMenu
      SELECT NavMenuID,
             SortOrder,
             ContentID,
             FilePath,
             WebsiteKey,
             PreviousWebsiteKey,
             MicrositeFlag
        FROM Nav_Menu WITH (NOLOCK)
       WHERE WebsiteKey = @InWebsiteKey
         AND NavContentGroupInd = 'N'
      ORDER BY SortOrder
    END

    -- For alll these nav items, let's update the status.
    -- if it's not in Published status yet.
    UPDATE a
       SET WorkflowStatusCode = 'P',
           PublishedDateTime = CURRENT_TIMESTAMP,
           PublishedFlag = 'Y',
           ReminderSentDateTime = NULL,
           LastUpdatedByContactID = @InContactID
      FROM Nav_Menu a, #NavMenu b
     WHERE a.NavMenuID = b.NavMenuID
       AND a.WorkflowStatusCode <> 'P'

    INSERT INTO Nav_Menu_Workflow_Log (
           NavMenuID,
           ContactID,
           WorkflowStatusCode,
           ChangeDateTime)
    SELECT a.NavMenuID,
           @InContactID,
           'P',
           CURRENT_TIMESTAMP
      FROM #NavMenu a, Nav_Menu b WITH (NOLOCK)
     WHERE a.NavMenuID = b.NavMenuID
       AND b.WorkflowStatusCode <> 'P'

    /*  At this point we want to get the content records that are either:
        1) A content Record that is in the 'A'pproved status for a Nav Item being published, or
        2) The default content Record that is in the 'P'ublished status with no new version in 'A'pproved status
          OR if attributes.ForcePublish EQ "Yes" then all content records, not just the default.
        3) A content Record in the 'W'orking status for a MicroSite
      For each Nav Item, Sort will ensure the non-default records are published first. */
  
    DECLARE c_ContentRecords CURSOR FOR
    SELECT a.NavMenuID,
           a.WebsiteKey,
           a.PreviousWebsiteKey,
           b.ContentID,
           b.PublishFrequency,
           b.PreFuseURL,
           b.PostFuseURL
      FROM (Content b WITH (NOLOCK) LEFT OUTER JOIN Nav_Menu c WITH (NOLOCK)
        ON b.ContentID = c.ContentID), #NavMenu a WITH (NOLOCK)
     WHERE a.NavMenuID = b.NavMenuID
       AND ( b.WorkflowStatusCode = 'A'
             OR ( b.WorkflowStatusCode = 'P'
                   AND ISNULL(a.MicrositeFlag, 'N') = 'N'
                 AND 1 = CASE WHEN @InForcePublishFlag ='N' AND c.ContentID IS NULL AND a.PreviousWebsiteKey IS NULL THEN 0
                     ELSE 1 END
                 AND NOT EXISTS ( SELECT 1
                                    FROM Content x WITH (NOLOCK)
                                   WHERE x.PreviousContentID = b.ContentID
                                     AND x.WorkflowStatusCode = 'A' )
               )
             OR ( b.WorkflowStatusCode = 'W' AND
                 a.MicrositeFlag = 'Y'
               )
            )
     ORDER BY a.SortOrder, IsNULL(c.ContentID, 0)

  END
  -- Regenerating content that have been affected by a change.
  ELSE BEGIN
    CREATE TABLE #Regenerate (NavMenuID numeric)
  
    EXEC amsp_CMGetNavMenuToRegenerate @InNavMenuID

    /* Let's get all the content records in the 'P'ublished status.  It is important
       to get the non-default records first so when we publish the List or Combo
       default records, the links/titles for the non-default records will be correct. */
    DECLARE c_ContentRecords CURSOR FOR
    SELECT n.NavMenuID,
           n.WebsiteKey,
           n.PreviousWebsiteKey,
           b.ContentID,
           b.PublishFrequency,
           b.PreFuseURL,
           b.PostFuseURL
      FROM (Content b WITH (NOLOCK) LEFT OUTER JOIN Nav_Menu c WITH (NOLOCK)
        ON b.ContentID = c.ContentID)
      LEFT OUTER JOIN Content x WITH (NOLOCK)
        ON b.ContentID = x.PreviousContentID, Nav_Menu n WITH (NOLOCK)
     WHERE b.NavMenuID = n.NavMenuID
       AND b.WorkflowStatusCode = 'P'
       AND n.NavMenuID IN (SELECT NavMenuID FROM #Regenerate)
       AND x.ContentID IS NULL
     ORDER BY n.SortOrder, IsNULL(c.ContentID, 0)
  END


  OPEN c_ContentRecords

  FETCH NEXT FROM c_ContentRecords
   INTO @NavMenuID,
        @WebsiteKey,
        @PreviousWebsiteKey,
        @ContentID,
        @PublishFrequency,
        @PreFuseURL,
        @PostFuseURL

  WHILE @@FETCH_STATUS = 0 BEGIN

    -- Make sure PreFuseURL and PostFuseURL is up to date.
    EXEC amsp_CMGetFuseURL @NavMenuID, @ContentID, @NewPreFuseURL OUTPUT, @NewPostFuseURL OUTPUT

    IF IsNull(@NewPreFuseURL,'') <> IsNull(@PreFuseURL,'')
       OR IsNull(@NewPostFuseURL,'') <> IsNull(@PostFuseURL,'') BEGIN
      UPDATE Content
         SET PreFuseURL = @NewPreFuseURL,
             PostFuseURL = @NewPostFuseURL
       WHERE ContentID = @ContentID
    END
    -- Publishing server for navigation items are determined by the Website.
    INSERT INTO Publish_Request_Detail
                (PublishRequestID,
                 ContentID,
                 PublishRegenerateInd,
                 PublishServerCode,
                 Frequency,
                 WebsiteKey)
    SELECT @PublishRequestID,
           @ContentID,
           @InPublishRegenerateInd,
           a.PublishServerCode,
           IsNull(@PublishFrequency,0) * 3600,
           a.WebsiteKey
      FROM Website a WITH (NOLOCK) LEFT OUTER JOIN Publish_Request_Detail x WITH (NOLOCK)
        ON a.WebsiteKey = x.WebsiteKey
       AND x.ContentID = @ContentID
       AND (x.PublishRequestStatusCode = 'N' OR x.Frequency > 0)
       AND x.PublishRegenerateInd = @InPublishRegenerateInd
     WHERE a.WebsiteKey = @WebsiteKey
       AND x.PublishRequestDetailID IS NULL

   -- If website key has been changed since the last publish, create another line
   -- to delete old files.
   IF @PreviousWebsiteKey IS NOT NULL AND @PreviousWebsiteKey <> @WebsiteKey BEGIN
      DELETE FROM Publish_Request_Detail
       WHERE WebsiteKey = @PreviousWebsiteKey
         AND ContentID = @ContentID
  
      INSERT INTO Publish_Request_Detail
                  (PublishRequestID,
                   ContentID,
                   PublishRegenerateInd,
                   PublishServerCode,
                   Frequency,
                   WebsiteKey,
                   DeleteFlag)
      SELECT @PublishRequestID,
             @ContentID,
             @InPublishRegenerateInd,
             PublishServerCode,
             0,
             WebsiteKey,
             'Y'
        FROM Website a WITH (NOLOCK)
       WHERE WebsiteKey = @PreviousWebsiteKey
    END
    /*  Commented out. Might be used in future versions.
    --Content from bucket may be published to more than 1 servers.
    ELSE BEGIN
      INSERT INTO Publish_Request_Detail
                  (PublishRequestID,
                   ContentID,
                   PublishRegenerateInd,
                   PublishServerCode,
                   Frequency)
      SELECT @PublishRequestID,
             @ContentID,
             @InPublishRegenerateInd,
             PublishServerCode,
             CASE WHEN @InPublishRegenerateInd = 'R' THEN 0
             ELSE IsNull(@PublishFrequency,0) * 3600 END
        FROM Content_Publish_Server a
       WHERE ContentID = @ContentID
         AND NOT EXISTS (SELECT 1
                           FROM Publish_Request_Detail x
                          WHERE ContentID = @ContentID
                            AND (PublishedDateTime IS NULL OR Frequency > 0)
                            AND x.PublishServerCode = a.PublishServerCode
                            AND x.PublishRegenerateInd = @InPublishRegenerateInd)
       ORDER BY a.DefaultServerFlag DESC
    END      
    */
    FETCH NEXT FROM c_ContentRecords
     INTO @NavMenuID,
          @WebsiteKey,
          @PreviousWebsiteKey,
          @ContentID,
          @PublishFrequency,
          @PreFuseURL,
          @PostFuseURL

  END -- End while loop

  CLOSE c_ContentRecords
  DEALLOCATE c_ContentRecords  

  
  IF @InPublishRegenerateInd = 'P' BEGIN

    -- Let's also update PreFuseURL and PostFuseURL.
    DECLARE c_MenuItems CURSOR FOR
    SELECT a.NavMenuID,
           (SELECT Count(*)
              FROM Content b WITH (NOLOCK), Publish_Request_Detail c WITH (NOLOCK)
             WHERE a.NavMenuID = b.NavMenuID
               AND b.ContentID = c.ContentID
               AND c.PublishRequestID = @PublishRequestID)
      FROM #NavMenu a WITH (NOLOCK)

    OPEN c_MenuItems

    FETCH NEXT FROM c_MenuItems
     INTO @NavMenuID,
          @ContentCount
    
    WHILE @@FETCH_STATUS = 0 BEGIN
      EXEC amsp_CMGetFuseURL @NavMenuID, DEFAULT, @NewPreFuseURL OUTPUT, @NewPostFuseURL OUTPUT

      -- If there are no content associated with the nav item, update PostURL too.
      UPDATE Nav_Menu
         SET PreFuseURL = @NewPreFuseURL,
             PostFuseURL = CASE WHEN @ContentCount = 0 THEN @NewPostFuseURL
                           ELSE PostFuseURL END
       WHERE NavMenuID = @NavMenuID
      
      -- If no content is associated, see if we need to regenerate any nav items.
      IF @ContentCount = 0
        EXECUTE amsp_CMRequestPublish @NavMenuID, NULL, @InContactID, 'N', 'Y', 'R', DEFAULT, @PublishRequestID, NULL

      FETCH NEXT FROM c_MenuItems
       INTO @NavMenuID,
            @ContentCount
    END

    CLOSE c_MenuItems
    DEALLOCATE c_MenuItems
  END

  SET @OutPublishRequestID = @PublishRequestID
END

GO
GRANT EXECUTE ON  [dbo].[amsp_CMRequestPublish] TO [IMIS]
GO